Lab 03 Preparing Data for Visualization

• Lab 03-A: Tidy Data
  • Data Preparation
  • Data Tidying
  • Missing Values
• Lab 03-B: Exploratory Data Analysis in R
  • Skim Datasets
  • First use of ggplot2
  • Distribution of Univariate Variable
  • Relationship Between Multivariate Variables
  • 📚 TODO: Review Diagrams
  • 📚 TODO: Explore National Mortgage Database
• Acknowledgement

In lab 3, we are going to explore tidy data, start to visualize data using R, and look at GitHub. The primary dataset we are using is Zillow Home Value Index (ZHVI), a measure of the typical home value and market changes across a given region and housing type. More information about what ZHVI is and how it’s calculated is available on this overview page.

The due day for each lab can be found on the course wesbite. The submission should include Rmd, html, and any other files required to rerun the codes. For Lab 1–3, the use of any generative AI tool (ChatGPT, Copilot, etc.) is prohibited. We will run AI detection on each submission. More information about Academic Integrity and the Use of AI.

Recommended Reading
Chapter 2, 6, Modern Data Science with R. Baumer et al. 2024.
Tidy Data, The Journal of Statistical Software, Hadley Wickham, 2014.

Lab 03-A: Tidy Data

library('tidyverse')
library('ggplot2') # the major package for visualization in R

Data Preparation

Instead of using API or R data package as we did in Lab 1 and 2, we will use data on our local device. We have organized the Zillow Home Value Index (ZHVI) for the largest 30 Metropolitan Statistical Ares (MSA) and their 2024 population in csv file zillow_hvi_msa.csv. Comma-separated values (CSV) is a text data format that uses commas to separate delimiter-separated values, and newlines to separate records.

hvi <- read_csv("zillow_hvi_msa.csv")

## Rows: 30 Columns: 313
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr   (3): RegionName, StateName, Region
## dbl (309): RegionID, 1/31/00, 2/29/00, 3/31/00, 4/30/00, 5/31/00, 6/30/00, 7...
## num   (1): Popu2024
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

CSV files are just plain text. You can use your text editor (TextEdit on Mac and Notepad on Windows) to have a look. They do not store metadata such as column types, units, or formats. So. when we use read_csv(), it automatically guesses column types by inspecting the data. Sometimes its guess is wrong, we need to specify the types using spec().

hvi <- read_csv("zillow_hvi_msa.csv",
  col_types = cols(
    RegionName = col_character(),
    StateName  = col_character(),
    Region   = col_character(), # Census Bureau Regions
    RegionID   = col_double(),
    Popu2024   = col_number(),
    .default   = col_double()   # for other columns
  )
)
head(hvi, 3)

## # A tibble: 3 × 313
##   RegionID RegionName    Popu2024 StateName Region `1/31/00` `2/29/00` `3/31/00`
##      <dbl> <chr>            <dbl> <chr>     <chr>      <dbl>     <dbl>     <dbl>
## 1   394913 New York, NY  19940274 NY        North…   220835.   221773.   222720.
## 2   753899 Los Angeles,… 12927614 CA        West     222016.   222842.   223942.
## 3   394463 Chicago, IL    9408576 IL        Midwe…   156058.   156202.   156478.
## # ℹ 305 more variables: `4/30/00` <dbl>, `5/31/00` <dbl>, `6/30/00` <dbl>,
## #   `7/31/00` <dbl>, `8/31/00` <dbl>, `9/30/00` <dbl>, `10/31/00` <dbl>,
## #   `11/30/00` <dbl>, `12/31/00` <dbl>, `1/31/01` <dbl>, `2/28/01` <dbl>,
## #   `3/31/01` <dbl>, `4/30/01` <dbl>, `5/31/01` <dbl>, `6/30/01` <dbl>,
## #   `7/31/01` <dbl>, `8/31/01` <dbl>, `9/30/01` <dbl>, `10/31/01` <dbl>,
## #   `11/30/01` <dbl>, `12/31/01` <dbl>, `1/31/02` <dbl>, `2/28/02` <dbl>,
## #   `3/31/02` <dbl>, `4/30/02` <dbl>, `5/31/02` <dbl>, `6/30/02` <dbl>, …

We will not see the message if we specified the data types. let’s have a look of the first 3 rows of this data. The data is ZHVI All Home (SFR, Condo/Co-op) Time Series, Smoothed, Seasonally Adjusted from Zillow Research. The data includes ZHVI for 30 MSA from 2000 to 2025 by month!

Currently, the hvi dataset has 30 rows and 312 columns. This presentation of the data has some advantages: we can easily look through the index for single city by date (one row). But, we have 308 columns for house value index, which treated as separate variables now. In data science, researchers defined a particular format of data table: tidy data - A research paper about tidy data.

Data Tidying

This section was adopted from Tidy data page, authored by Hadley Wickham, I believe.

Happy families are all alike; every unhappy family is unhappy in its own way — Leo Tolstoy

Like families, tidy datasets are all alike but every messy dataset is messy in its own way. The principles of tidy data provide a standard way to organize data values within a dataset. A standard makes initial data cleaning easier because you don’t need to start from scratch and reinvent the wheel every time. The tidy data standard has been designed to facilitate initial exploration and analysis of the data, and to simplify the development of data analysis tools that work well together.

Data Semantics

A dataset is a collection of values, usually either numbers or strings. Values are organised in two ways: every value belongs to a variable and an observation.

• A variable contains all values that measure the same underlying attribute (like height, temperature, duration) across units.
• An observation contains all values measured on the same unit (like a person, or a day, or a race) across attributes.

head(hvi, 3)

## # A tibble: 3 × 313
##   RegionID RegionName    Popu2024 StateName Region `1/31/00` `2/29/00` `3/31/00`
##      <dbl> <chr>            <dbl> <chr>     <chr>      <dbl>     <dbl>     <dbl>
## 1   394913 New York, NY  19940274 NY        North…   220835.   221773.   222720.
## 2   753899 Los Angeles,… 12927614 CA        West     222016.   222842.   223942.
## 3   394463 Chicago, IL    9408576 IL        Midwe…   156058.   156202.   156478.
## # ℹ 305 more variables: `4/30/00` <dbl>, `5/31/00` <dbl>, `6/30/00` <dbl>,
## #   `7/31/00` <dbl>, `8/31/00` <dbl>, `9/30/00` <dbl>, `10/31/00` <dbl>,
## #   `11/30/00` <dbl>, `12/31/00` <dbl>, `1/31/01` <dbl>, `2/28/01` <dbl>,
## #   `3/31/01` <dbl>, `4/30/01` <dbl>, `5/31/01` <dbl>, `6/30/01` <dbl>,
## #   `7/31/01` <dbl>, `8/31/01` <dbl>, `9/30/01` <dbl>, `10/31/01` <dbl>,
## #   `11/30/01` <dbl>, `12/31/01` <dbl>, `1/31/02` <dbl>, `2/28/02` <dbl>,
## #   `3/31/02` <dbl>, `4/30/02` <dbl>, `5/31/02` <dbl>, `6/30/02` <dbl>, …

Look at our hvi dataset, it contains 9,360 values representing 312 variables and 30 observations. The variables are:

• RegionID: a unique regional ID defined by Zillow
• RegionalName: the name of 30 MSA
• Popu2024: the population of MSA in 2024 based on US Census Bureau
• StateName: state name
• 308 columns representing dates: Housing Value Index on that date

A tidy version of the data looks like this:

hvi_tidy <- hvi %>%
  pivot_longer( # using `pivot_longer` to reshape wide → long
    cols = -c("RegionID", "RegionName", "Popu2024", "StateName", "Region"), # keep these 4 as identifier variables (don’t pivot them)
    names_to = "Date",  # old column names (like "1/31/00") become a new column called Date                 
    values_to = "HVI" # the cell values go into a new column called HVI 
    # values_drop_na = TRUE; default as FALSE; if TRUE, will remove rows with missing HVI.
  ) %>%
  mutate(
    Date = as.Date(Date, format = "%m/%d/%y")                   
  )
head(hvi_tidy,3)

## # A tibble: 3 × 7
##   RegionID RegionName   Popu2024 StateName Region    Date           HVI
##      <dbl> <chr>           <dbl> <chr>     <chr>     <date>       <dbl>
## 1   394913 New York, NY 19940274 NY        Northeast 2000-01-31 220835.
## 2   394913 New York, NY 19940274 NY        Northeast 2000-02-29 221773.
## 3   394913 New York, NY 19940274 NY        Northeast 2000-03-31 222720.

Look at our hvi_tidy dataset again, it contains 9,360 values representing 6 variables and 9,240 observations. The variables are:

• RegionID: a unique regional ID defined by Zillow
• RegionalName: the name of 30 MSA
• Popu2024: the population of MSA in 2024 based on US Census Bureau
• StateName: state name
• Date: NEW - observation date
• HVI: NEW - Housing Value Index from Zillow

The tidy data frame explicitly tells us the definition of an observation. Every combination of RegionName and Date is a single measured observation (HVI). It’s important to clearly define what is an observation. In the case of HVI, we are interested in HVI for sure! For an observation, such as 220834.8, we need know two information: region and date.

Tidy data

In tidy data:

• Each variable is a column; each column is a variable.
• Each observation is a row; each row is an observation.
• Each value is a cell; each cell is a single value.

Tidy data makes it easy for an analyst or a computer to extract needed variables because it provides a standard way of structuring a dataset. Tidy data is particularly well suited for vectorised programming languages like R, because the layout ensures that values of different variables from the same observation are always paired. Many R packages, such as ggplot2 are designed around the tidy data principles.

From Tidy Data to Wider Data

We can use pivot_wider() to perform the inverse operation.

hvi_wide <- hvi_tidy %>%
  pivot_wider(
    names_from = Date,   # expand `Date` to multiple rows 
    values_from = HVI    # extract`HVI` column to those new columns
  )
head(hvi_wide, 3)

## # A tibble: 3 × 313
##   RegionID RegionName      Popu2024 StateName Region   `2000-01-31` `2000-02-29`
##      <dbl> <chr>              <dbl> <chr>     <chr>           <dbl>        <dbl>
## 1   394913 New York, NY    19940274 NY        Northea…      220835.      221773.
## 2   753899 Los Angeles, CA 12927614 CA        West          222016.      222842.
## 3   394463 Chicago, IL      9408576 IL        Midwest       156058.      156202.
## # ℹ 306 more variables: `2000-03-31` <dbl>, `2000-04-30` <dbl>,
## #   `2000-05-31` <dbl>, `2000-06-30` <dbl>, `2000-07-31` <dbl>,
## #   `2000-08-31` <dbl>, `2000-09-30` <dbl>, `2000-10-31` <dbl>,
## #   `2000-11-30` <dbl>, `2000-12-31` <dbl>, `2001-01-31` <dbl>,
## #   `2001-02-28` <dbl>, `2001-03-31` <dbl>, `2001-04-30` <dbl>,
## #   `2001-05-31` <dbl>, `2001-06-30` <dbl>, `2001-07-31` <dbl>,
## #   `2001-08-31` <dbl>, `2001-09-30` <dbl>, `2001-10-31` <dbl>, …

Missing Values

In pivot_longer, there is an optional argument called values_drop_na =, default as FALSE. If setting as TRUE, it will remove rows with missing HVI. We set as FALSE but is there any NA (Not Available) values?

We can use is.na() to check and there are 3 missing value in HVI column:

colSums(is.na(hvi_tidy))

##   RegionID RegionName   Popu2024  StateName     Region       Date        HVI 
##          0          0          0          0          0          0          3

We can remove NA or smooth/impute them based on their neighbors. We will just remove them here because those 3 missing values do not impact too much on our visualization. But, there are many ways you can do data interpolation. It can based on known temporal neighbors, spatial neighbors, group-based neighbors. It goes beyond this course but it’s an important problem for data science. If you are interested in missing data, you can refer to this book: Statistical Analysis with Missing Data.

# remove NA then we can see there are 9237 rows afterwards, 3 missing values are removed
hvi_tidy_NA_remove <- hvi_tidy %>% 
  filter(!is.na(HVI))
dim(hvi_tidy_NA_remove)

## [1] 9237    7

📚 TODO: Handle Missing Values

4 points

Instead of omitting those missing values, let’s try filling missing values in hvi_tidy using a linear interpolation method. You can use the zoo package, which provides a convenient function na.approx() for handling time-series data.

The documentation and this post may be helpful to you!

# TODO
library(zoo)

## 
## Attaching package: 'zoo'

## The following objects are masked from 'package:base':
## 
##     as.Date, as.Date.numeric

hvi_tidy <- hvi_tidy %>%
  group_by(Date) %>%
  mutate(HVI = na.approx(HVI, na.rm = FALSE))
hvi_tidy

## # A tibble: 9,240 × 7
## # Groups:   Date [308]
##    RegionID RegionName   Popu2024 StateName Region    Date           HVI
##       <dbl> <chr>           <dbl> <chr>     <chr>     <date>       <dbl>
##  1   394913 New York, NY 19940274 NY        Northeast 2000-01-31 220835.
##  2   394913 New York, NY 19940274 NY        Northeast 2000-02-29 221773.
##  3   394913 New York, NY 19940274 NY        Northeast 2000-03-31 222720.
##  4   394913 New York, NY 19940274 NY        Northeast 2000-04-30 224640.
##  5   394913 New York, NY 19940274 NY        Northeast 2000-05-31 226627.
##  6   394913 New York, NY 19940274 NY        Northeast 2000-06-30 228830.
##  7   394913 New York, NY 19940274 NY        Northeast 2000-07-31 231238.
##  8   394913 New York, NY 19940274 NY        Northeast 2000-08-31 233801.
##  9   394913 New York, NY 19940274 NY        Northeast 2000-09-30 236425.
## 10   394913 New York, NY 19940274 NY        Northeast 2000-10-31 239042.
## # ℹ 9,230 more rows

Lab 03-B: Exploratory Data Analysis in R

Starting from this lab session, we are going to conduct data visualization using R and ggplot2. You don’t have to install ggplot2 separately because it is already a part of tidyverse.

Exploratory Data Analysis (EDA) is the process of summarizing, visualizing, and checking data to uncover patterns, detect outliers, and identify unexpected features. It helps you understand the structure and quality of your dataset before applying formal statistical models or machine learning methods. The key of EDA is not aesthetics, design, or storytelling, but rather developing an understanding of the data itself. However, for learning purposes, we will use ggplot2 because it provides a consistent and flexible framework for visualizing data in R. In realty, you can use any tool, base R plotting, Tableau, Excel, for EDA. What matters is the insight you gain, not the tool itself. This section is partially built based on Exploratory Data Analysis from EPA.

Skim Datasets

The first step of Exploratory Data Analysis (EDA) is to quickly check the structure of the dataset: variable types, missing values, and basic distributions. The skimr package provides the skim() function, which offers a richer summary than summary() we used before.

# install.packages("skimr") # you only need to install once
library(skimr)
skim(hvi_tidy) # run `skim()` to get a quick overview of the dataset

Variable type: character